<?php
/**
 * Created by PhpStorm.
 * User: lulanyin@qq.com
 * DateTime: 2016/1/13 11:30
 * Web: http://lanyin.lu
 * 操作数据库
 */
class QueryToSQL {

    public $grammar;                            //语法
    public $pdo;                                //PDO
    public $fetchModel = PDO::FETCH_ASSOC;      //遍历方式
    public $lastInsertId = [];                  //执行完成后，这个对象所产生的自增ID
    public $affectRows = 0;                     //执行完之后，这个对象所影响的数据行数
    public $sqlQuery = '';                      //生成的SQL语句

    public $connection;                         //对应的数据库连接类
    public $tableName = '';                     //查询的表名
    public $columns = ["*"];                    //查询的列名

    public $wheres = [];                        //条件
    public $joins = [];                         //关联查询
    public $joinAs = '';                        //关联查询时，如果有使用临时表，则需要给一个别名
    public $groupBy = [];                       //结果分组
    public $orderBy = [];                       //排序
    public $having = [];                        //侧重条件
    public $unions = [];                        //联合查询
    public $set = [];                           //插入、更新的时候，字段、字段值的信息

    public $error = '';                         //如果产生了错误，则记录了错误TEXT文本
    public $errorId = 0;                        //如果产生了错误，则记录了错误的ID

    public $result = [];                        //查询得到结果时的数据数组

    public $operators = [
        '=', '>', '>=', '<', '<=', '!=', '<>',
        'like', 'not like', 'like binary',
        'in', 'not in',
        'between', 'not between',
        'is null', 'is not null',
        'regexp', 'not regexp',
        'find_in_set', 'not find_in_set'
    ];                                          //对比符号限定


    /**
     * QueryToSQL constructor.
     * @param Connection $connection
     * @return QueryToSQL
     */
    public function __construct(Connection $connection){
        $this->connection = $connection;
        switch($this->connection->driver){
            case "mssql" :
                require_once __DIR__."/../grammar/mssqlGrammar.class.php";
                $this->grammar = new mssqlGrammar($this);
                break;
            case "sqlite" :
                require_once __DIR__."/../grammar/sqliteGrammar.class.php";
                $this->grammar = new sqliteGrammar($this);
                break;
            case "oracle" :
                require_once __DIR__."/../grammar/oracleGrammar.class.php";
                $this->grammar = new oracleGrammar($this);
                break;
            case "mysql" :
                require_once __DIR__."/../grammar/mysqlGrammar.class.php";
                $this->grammar = new mysqlGrammar($this);
                break;
            default :
                throw new InvalidArgumentException("error database driver name");
                break;
        }
        return $this;
    }
    protected function newQuery(){
        return new QueryToSQL($this->connection);
    }

    /**
     * select|update|delete|insert into [from]
     * @param string $tableName
     * @return QueryToSQL
     */
    public function from($tableName=''){
        $this->tableName = $tableName;
        return $this;
    }
    /**
     * select|update|delete|insert into [from]
     * @param string $tableName
     * @return QueryToSQL
     */
    public function table($tableName=''){
        return $this->from($tableName);
    }

    /**
     * select [columns] from ....
     * @param array $columns
     * @return QueryToSQL
     */
    public function select($columns=['*']){
        $this->columns = is_array($columns) ? $columns : func_get_args();
        return $this;
    }

    /**
     * add select fields
     * @param array $columns
     * @return QueryToSQL
     */
    public function addSelect($columns=[]){
        $columns = is_array($columns) ? $columns : func_get_args();
        $this->columns = array_merge($this->columns, $columns);
        return $this;
    }

    /**
     * join关联, 可嵌套
     * @param $tableName
     * @param $column
     * @param null $operator
     * @param null $value
     * @param string $type
     * @return QueryToSQL
     */
    public function join($tableName, $column='', $operator=null, $value=null, $type="inner"){
        $status = 'default';//默认
        $as = '';
        if(func_num_args()==3){
            list($operator, $value) = array("=", $operator);
        }
        if($tableName instanceof Closure){
            $join = $this->newQuery();
            call_user_func($tableName, $join);
            $queryString = $join->compileToQueryString();
            $as = $join->joinAs;
            $table = "({$queryString}) ".(!empty($as) ? $as : die("not temp table name"));
            $status = 'nested';//嵌套
        }else{
            $table = $tableName;
        }
        $this->joins[] = compact('table', 'as', 'column', 'operator', 'value', 'type', 'status');
        return $this;
    }
    public function leftJoin($tableName, $column='', $operator=null, $value=null){
        if(func_num_args()==3){
            list($operator, $value) = array("=", $operator);
        }
        return $this->join($tableName, $column, $operator, $value, 'left');
    }
    public function rightJoin($tableName, $column='', $operator=null, $value=null){
        if(func_num_args()==3){
            list($operator, $value) = array("=", $operator);
        }
        return $this->join($tableName, $column, $operator, $value, 'right');
    }
    public function innerJoin($tableName, $column='', $operator=null, $value=null){
        return $this->join($tableName, $column, $operator, $value);
    }

    /**
     * 关联查询时，子查询语句的临时表别名
     * @param $asTableName
     * @return QueryToSQL
     */
    public function joinAs($asTableName){
        $this->joinAs = $asTableName;
        return $this;
    }

    /**
     * where 条件设置
     * @param $column
     * @param null $operator
     * @param null $value
     * @param string $boolean
     * @return QueryToSQL
     * @throws InvalidArgumentException
     */
    public function where($column, $operator=null, $value=null, $boolean='and'){
        if(is_array($column)){
            $column = is_array(end($column)) ? $column : [$column];
            return $this->whereNested(function (QueryToSQL $query) use ($column, $boolean) {
                foreach ($column as $where) {
                    $col = $where[0];
                    if(!empty($col)){
                        $ope = isset($where[2]) ? $where[1] : "=";
                        $val = isset($where[2]) ? $where[2] : (isset($where[1]) ? $where[1] : '');
                        $bool = isset($where[3]) ? $where[3] : $boolean;
                        $query->where($col, $ope, $val, $bool);
                    }
                }
            }, $boolean);
        }

        if(func_num_args()==2){
            list($operator, $value) = ['=', $operator];
        }elseif($this->invalidOperatorAndValue($operator, $value)){
            throw new InvalidArgumentException('error where operator');
        }

        if($column instanceof Closure){
            return $this->whereNested($column, $boolean);
        }

        if (! in_array(strtolower($operator), $this->operators, true)) {
            list($value, $operator) = [$operator, '='];
        }

        if($value instanceof Closure){
            return $this->whereSub($column, $operator, $value, $boolean);
        }

        if (is_null($value)) {
            return $this->whereNull($column, $boolean, $operator != '=');
        }

        $type = 'basic';
        $this->wheres[] = compact('type', 'column', 'operator', 'value', 'boolean');
        return $this;
    }

    /**
     * or where 条件
     * @param $column
     * @param null $operator
     * @param null $value
     * @return QueryToSQL
     */
    public function orWhere($column, $operator=null, $value=null){
        if(func_num_args()==2){
            list($value, $operator) = array($operator, "=");
        }
        return $this->where($column, $operator, $value, 'or');
    }

    /**
     * @param $sql
     * @param string $boolean
     * @return $this
     */
    public function whereRaw($sql, $boolean='and'){
        $type = 'Raw';
        $this->wheres[] = compact('type', 'sql', 'boolean');
        return $this;
    }
    public function orWhereRaw($sql){
        return $this->whereRaw($sql, 'or');
    }

    /**
     * 添加一个子查询
     * @param $column
     * @param $operator
     * @param Closure $callback
     * @param $boolean
     * @return QueryToSQL
     */
    public function whereSub($column, $operator, Closure $callback, $boolean='and'){
        $type = 'Sub';
        $query = $this->newQuery();
        call_user_func($callback, $query);
        $this->wheres[] = compact('type', 'column', 'operator', 'query', 'boolean');
        return $this;
    }

    /**
     * 嵌套查询
     * @param Closure $callBack
     * @param string $boolean
     * @return QueryToSQL
     */
    public function whereNested(Closure $callBack, $boolean='and'){
        $query = $this->newQuery();
        $query->from($this->tableName);
        call_user_func($callBack, $query);
        return $this->addNestedWhereQuery($query, $boolean);
    }

    /**
     *
     * @param $query
     * @param string $boolean
     * @return QueryToSQL
     */
    public function addNestedWhereQuery($query, $boolean='and'){
        if (count($query->wheres)) {
            $type = 'Nested';
            $this->wheres[] = compact('type', 'query', 'boolean');
        }
        return $this;
    }

    /**
     * 字段为空的条件
     * @param $column
     * @param string $boolean
     * @param bool $not
     * @return QueryToSQL
     */
    public function whereNull($column, $boolean='and', $not=false){
        $type = $not ? 'NotNull' : 'Null';
        $this->wheres[] = compact('type', 'column', 'boolean');
        return $this;
    }
    public function orWhereNull($column, $not=false){
        return $this->whereNull($column, 'or', $not);
    }
    public function whereNotNull($column, $boolean='and'){
        return $this->whereNull($column, $boolean, true);
    }
    public function orWhereNotNull($column){
        return $this->orWhereNull($column, true);
    }

    /**
     * @param $column
     * @param $value
     * @param string $boolean
     * @param bool $not
     * @return QueryToSQL
     */
    public function whereIn($column, $value, $boolean='and', $not=false){
        if($value instanceof Closure){
            return $this->whereInSub($column, $value, $boolean, $not);
        }
        $type = $not ? 'NotIn' : 'In';
        $this->wheres[] = compact('type', 'column', 'value', 'boolean');
        return $this;
    }

    /**
     * in 条件
     * @param $column
     * @param Closure $callback
     * @param $boolean
     * @param $not
     * @return QueryToSQL
     */
    public function whereInSub($column, Closure $callback, $boolean='and', $not=false){
        $type = $not ? 'NotIn' : 'In';
        $query = $this->newQuery();
        call_user_func($callback, $query);
        $this->wheres[] = compact('type', 'column', 'query', 'boolean');
        return $this;
    }

    public function whereNotIn($column, $value, $boolean='and'){
        return $this->whereIn($column, $value, $boolean, true);
    }
    public function orWhereIn($column, $value, $not=false){
        return $this->whereIn($column, $value, 'or', $not);
    }
    public function orWhereNotIn($column, $value){
        return $this->orWhereIn($column, $value, true);
    }

    /**
     * between 条件
     * @param $column
     * @param $value
     * @param string $boolean
     * @param bool $not
     * @return QueryToSQL
     */
    public function whereBetween($column, $value, $boolean='and', $not=false){
        $type = $not ? 'NotBetween' : 'Between';
        $this->wheres[] = compact('type', 'column', 'value', 'boolean');
        return $this;
    }
    public function whereNotBetween($column, $value, $boolean='and'){
        return $this->whereBetween($column, $value, $boolean, true);
    }
    public function orWhereBetween($column, $value, $not=false){
        return $this->whereBetween($column, $value, 'or', $not);
    }
    public function orWhereNotBetween($column, $value){
        return $this->orWhereBetween($column, $value, true);
    }

    /**
     * exists条件
     * @param $value
     * @param string $boolean
     * @param bool $not
     * @return QueryToSQL
     */
    public function whereExists($value, $boolean='and', $not=false){
        if($value instanceof Closure){
            return $this->whereExistsSub($value, $boolean, $not);
        }else{
            return $this;
        }
    }
    public function whereNotExists($value, $boolean='and'){
        return $this->whereExists($value, $boolean, true);
    }
    public function orWhereExists($value, $not=false){
        return $this->whereExists($value, 'or', $not);
    }
    public function orWhereNotExists($value){
        return $this->orWhereExists($value, true);
    }
    public function whereExistsSub(Closure $callback, $boolean='and', $not=false){
        $type = $not ? 'NotExists' : 'Exists';
        $query = $this->newQuery();
        call_user_func($callback, $query);
        $this->wheres[] = compact('type', 'query', 'boolean');
        return $this;
    }

    /**
     * find_in_set
     * @param $value
     * @param $column
     * @param string $boolean
     * @return QueryToSQL
     */
    public function whereFindInSet($value, $column, $boolean='and'){
        $type = 'FindInSet';
        $this->wheres[] = compact('type', 'column', 'value', 'boolean');
        return $this;
    }
    public function orWhereFindInSet($value, $column){
        return $this->whereFindInSet($value, $column);
    }

    /**
     * like 查询条件
     * @param $column
     * @param $value
     * @param string $boolean
     * @param bool $not
     * @return QueryToSQL
     */
    public function whereLike($column, $value, $boolean='and', $not=false){
        $type = $not ? 'NotLike' : 'Like';
        $this->wheres[] = compact('type', 'column', 'value', 'boolean');
        return $this;
    }
    public function orWhereLike($column, $value, $not=false){
        return $this->whereLike($column, $value, "or", $not);
    }
    public function whereNotLike($column, $value){
        return $this->whereLike($column, $value, 'and', true);
    }
    public function orWhereNotLike($column, $value){
        return $this->orWhereLike($column, $value, true);
    }

    /**
     * 正则匹配
     * @param $column
     * @param $value
     * @param string $boolean
     * @param bool $not
     * @return QueryToSQL
     */
    public function whereRegexp($column, $value, $boolean='and', $not=false){
        $type = $not ? 'NotRegexp' : 'Regexp';
        $this->wheres[] = compact('type', 'column', 'value', 'boolean');
        return $this;
    }
    public function orWhereRegexp($column, $value, $not=false){
        return $this->whereRegexp($column, $value, "or", $not);
    }
    public function whereNotRegexp($column, $value){
        return $this->whereRegexp($column, $value, 'and', true);
    }
    public function orWhereNotRegexp($column, $value){
        return $this->orWhereRegexp($column, $value, true);
    }

    /**
     * union
     * @param QueryToSQL $query
     * @param bool $all
     * @return QueryToSQL
     */
    public function union(QueryToSQL $query, $all=false){
        $this->unions[] = compact('query', 'all');
        return $this;
    }
    public function unionAll(QueryToSQL $query){
        return $this->union($query);
    }

    /**
     * @param $column
     * @param $operator
     * @param null $value
     * @return QueryToSQL
     */
    public function having($column, $operator, $value=null){
        if(func_num_args()==2){
            list($value, $operator) = [$operator, '='];
        }
        $this->having[] = compact('column', 'operator', 'value');
        return $this;
    }

    /**
     * group by
     * @param $columns
     * @return QueryToSQL
     */
    public function groupBy($columns){
        $this->groupBy[] = $columns;
        return $this;
    }

    /**
     * order by
     * @param $column
     * @param null $type
     * @return QueryToSQL
     */
    public function orderBy($column, $type=null){
        if(is_array($column)){
            foreach($column as $col){
                if(!empty($col[0])){
                    $this->orderBy[] = array(
                        "column" => $col[0],
                        "type" => isset($col[1]) ? $col[1] : "ASC"
                    );
                }
            }
        }else{
            $this->orderBy[] = is_array($column) ? $column : compact('column', 'type');
        }
        return $this;
    }

    /**
     * 解析成SQL语句
     * @param string $type
     * @return string
     */
    public function compileToQueryString($type='select'){
        $query = $this->grammar->compileToQueryString($type);
        return is_array($query) ? reset($query) : $query;
    }

    /**
     * 读取数据
     * @param int $total
     * @param int $offset
     * @return QueryToSQL|bool
     */
    public function get($total=1000, $offset=0){
        $this->result = $this->grammar->get($total, $offset);
        return $this;
    }

    /**
     * 读取第一条数据
     * @return QueryToSQL
     */
    public function first(){
        $row = $this->get(1)->toArray();
        return is_array(end($row)) ? end($row) : [];
    }
    public function getOn(){
        return $this->first();
    }

    /**
     * 读取前X条
     * @param $total
     * @return QueryToSQL|bool
     */
    public function top($total){
        return $this->get($total);
    }

    public function count(){
        return $this->grammar->count();
    }
    public function rows(){
        return $this->count();
    }

    /**
     * @param $column
     * @param null $value
     * @param bool $valueIncludeField
     * @return QueryToSQL
     */
    public function set($column, $value=null, $valueIncludeField=false){
        //[0 => ['field'=>'', 'value'=>'', 'includeField'=>true] ]
        if(is_array($column)){
            foreach($column as $key=>$val){
                $this->set[] = [
                    'field' => $key,
                    'value' => $val,
                    'include_field' => $value===true || $value===false ? $value : $valueIncludeField
                ];
            }
        }else{
            $this->set[] = [
                'field' => $column,
                'value' => $value,
                'include_field' => $valueIncludeField
            ];
        }
        return $this;
    }

    /************************** 开始执行方法 **************************/

    /**
     * 执行更新数据库语句
     * @param null $columns
     * @return bool | QueryToSQL
     */
    public function update($columns=null){
        if(is_array($columns)){
            $this->set($columns);
        }
        if($this->grammar->update()){
            return $this;
        }
        return false;
    }

    /**
     * @param null $columns
     * @return bool | QueryToSQL
     */
    public function updateInsert($columns=null){
        $firstRow = $this->first();
        if(!empty($firstRow)){
            return $this->update($columns);
        }else{
            return $this->insert($columns);
        }
    }

    /**
     * 执行插入数据操作
     * @param null $set
     * @return bool | QueryToSQL
     */
    public function insert($set=null){
        if(is_array($set)){
            $this->set($set);
        }
        if($this->grammar->insert()){
            return $this;
        }
        return false;
    }

    /**
     * 插入数据并返回自增ID，失败时返回0
     * @param null $set
     * @return array|int|mixed
     */
    public function insertGetId($set=null){
        if($this->insert($set)){
            return $this->getLastInsertId();
        }else{
            return 0;
        }
    }

    /**
     * 获取自增ID
     * @return array|int|mixed
     */
    public function getLastInsertId(){
        return count($this->lastInsertId) > 1 ? $this->lastInsertId : (!empty($this->lastInsertId) ? end($this->lastInsertId) : 0);
    }

    /**
     * 删除数据
     * @return bool | QueryToSQL
     */
    public function delete(){
        if($this->grammar->delete()){
            return $this;
        }
        return false;
    }

    /**
     * 复制数据到表
     * @param $table
     * @param int $total
     * @param int $offset
     * @param array $unCopyFields
     * @param array $resetFields
     * @param $fn -- 复制完1条之后要执行的方法
     * @return bool|QueryToSql
     */
    public function copyTo($table, array $unCopyFields=array(), array $resetFields=array(), $total=1, $offset=0, $fn=null){
        $data = $this->get($total, $offset);
        if(!empty($data)){
            foreach($data as $key=>$d){
                //删除不需要的字段，类似表的主键
                foreach($unCopyFields as $uf){
                    if(isset($d[$uf])) unset($d[$uf]);
                }
                //重设、补加一些字段值，需要改变某些值字段的时候，很方便
                foreach($resetFields as $field=>$value){
                    $d[$field] = $value;
                }
                $query = $this->newQuery()->from($table);
                if($query->insert($d)){
                    $this->lastInsertId[] = $last_insert_id = $query->getLastInsertId();
                    if($fn instanceof Closure){
                        call_user_func($fn, $last_insert_id, $key);
                    }
                }else{
                    $this->error = $query->error;
                    $this->errorId = $query->errorId;
                    return false;
                }
            }
        }
        return $this;
    }

    /**
     * 获取影响的记录条数
     * @return int
     */
    public function getAffectRows(){
        return $this->affectRows;
    }

    /**
     * 获取错误
     * @return string
     */
    public function getError(){
        return $this->error;
    }

    /**
     * 从数据表中取得单一数据列的单一字段
     * @param string $column
     * @return string
     */
    public function pluck($column){
        $this->columns = [$column];
        $firstRow = $this->first();
        return isset($firstRow[$column]) ? $firstRow[$column] : "";
    }

    /**
     * 取得单一字段值的列表
     * @param string $column
     * @param bool $distinct
     * @return array
     */
    public function lists($column, $distinct=false){
        $this->columns = $distinct ? ["distinct {$column} as list_column"] : ["{$column} as list_column"];
        $rows = $this->get()->toArray();
        $list = array();
        foreach($rows as $r){
            if(isset($r["list_column"])){
                $list[] = $r["list_column"];
            }
        }
        return $list;
    }

    /**
     * 获取聚合函数统计的值
     * @param $column
     * @param $fn
     * @return string
     */
    protected function aggregateFunction($column, $fn){
        $string = $fn."({$column}) as val";
        $this->columns = [$string];
        $row = $this->first();
        //echo $this->sql;
        return isset($row["val"]) ? $row["val"] : "";
    }

    /**
     * 获取字段最大值
     * @param $column
     * @return string
     */
    public function max($column){
        return $this->aggregateFunction($column, "max");
    }

    /**
     * 获取字段的最小值
     * @param $column
     * @return string
     */
    public function min($column){
        return $this->aggregateFunction($column, "min");
    }

    /**
     * 获取平均值
     * @param $column
     * @param $round
     * @return string
     */
    public function avg($column, $round=2){
        $string = "round(avg(`{$column}`), {$round}) as val";
        $this->columns = [$string];
        $row = $this->first();
        return isset($row["val"]) ? $row["val"] : "";
    }

    /**
     * 获取总和
     * @param $column
     * @return string
     */
    public function sum($column){
        return $this->aggregateFunction($column, "sum");
    }

    /**
     * 快速清空数据表，并重置自增ID
     * @return bool|Grammar
     */
    public function truncate(){
        return $this->grammar->truncate();
    }

    /**
     * 检查条件对比符号是否合法
     * @param $operator
     * @param $value
     * @return bool
     */
    protected function invalidOperatorAndValue($operator, $value){
        $isOperator = in_array($operator, $this->operators);
        return $isOperator && $operator != '=' && is_null($value);
    }

    /**
     * 返回查询结果
     * @return array
     */
    public function toArray(){
        return $this->result;
    }

    /**
     * 将结果返回为JSON数据格式
     * @param int $int
     * @return string
     */
    public function toJson($int = JSON_UNESCAPED_UNICODE){
        return json_encode($this->result, $int);
    }

    /**
     * 内置的结果数据遍历方法
     * @param Closure $callback
     */
    public function each(Closure $callback){
        foreach($this->result as $key=>$row){
            call_user_func($callback, $row, $key);
        }
    }

    /**
     * 原生SQL赋值
     * @param $column
     * @param $value
     * @param null $set
     * @return QueryToSQL
     */
    public function setRaw($column, $value, $set=null){
        if(is_array($set)){
            @$this->set($set);
        }
        return $this->set($column, $value, true);
    }
    /**
     * 自增一个字段，可以同时更新其它字段
     * @param $column
     * @param int $number
     * @param null $set
     * @return QueryToSQL
     */
    public function increment($column, $number=1, $set=null){
        return $this->setRaw($column, "`{$column}`+{$number}", $set);
    }

    /**
     * 自减一个字段，可以同时更新其它字段
     * @param $column
     * @param int $number
     * @param null $set
     * @return $this
     */
    public function decrement($column, $number=1, $set=null){
        return $this->setRaw($column, "`{$column}`-{$number}", $set);
    }
}